package nuc.ss.wl.dao.impl;

import nuc.ss.wl.dao.StudentDao;
import nuc.ss.wl.entity.Student;
import nuc.ss.wl.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class StudentDaoImpl implements StudentDao {

    @Override
    public int save(Student student) {
        String sql = "insert into student values(?,?,?,?,?,?,?,?)";
        Object[] params = {
                student.getSno(),
                student.getSname(),
                student.getSpassword(),
                student.getSsalt(),
                student.getClassno(),
                student.getSsex(),
                student.getSage(),
                student.getSphone()
        };
        return DBUtil.executeUpdate(sql, params);
    }

    @Override
    public int delete(int sno) {
        String sql = "delete from student where sno = ?";
        Object[] params = {
                sno
        };
        return DBUtil.executeUpdate(sql, params);
    }

    @Override
    public int update(Student student) {
        String sql = "update student set sname = ?, spassword = ?, ssalt = ?, "
                + "classno = ?, ssex = ?, sage = ?, sphone = ? where sno = ?";
        Object[] params = {
                student.getSname(),
                student.getSpassword(),
                student.getSsalt(),
                student.getClassno(),
                student.getSsex(),
                student.getSage(),
                student.getSphone(),
                student.getSno()
        };
        return DBUtil.executeUpdate(sql, params);
    }

    @Override
    public List<Student> findAll() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        //声明变量
        Student student = null;
        List<Student> studentList = new ArrayList<>();
        conn = DBUtil.getConnection();
        String sql = "select * from student order by sno desc";
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                int sno = rs.getInt("sno");
                String sname = rs.getString("sname");
                String spassword = rs.getString("spassword");
                String ssalt = rs.getString("ssalt");
                int classno = rs.getInt("classno");
                String ssex = rs.getString("ssex");
                int sage = rs.getInt("sage");
                String sphone = rs.getString("sphone");
                student = new Student(sno, sname, spassword, ssalt, classno, ssex, sage, sphone);
                studentList.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(rs, ps, conn);
        }
        return studentList;
    }

    @Override
    public Student findBySno(int sno) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        //声明变量
        Student student = null;
        conn = DBUtil.getConnection();
        String sql = "select * from student where sno = ?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, sno);
            rs = ps.executeQuery();
            while (rs.next()) {
                String sname = rs.getString("sname");
                String spassword = rs.getString("spassword");
                String ssalt = rs.getString("ssalt");
                int classno = rs.getInt("classno");
                String ssex = rs.getString("ssex");
                int sage = rs.getInt("sage");
                String sphone = rs.getString("sphone");
                student = new Student(sno, sname, spassword, ssalt, classno, ssex, sage, sphone);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(rs, ps, conn);
        }
        return student;
    }


    @Override
    public String findSSaltBySno(int sno) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String ssalt = null;
        conn = DBUtil.getConnection();
        String sql = "select ssalt from student where sno = ?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, sno);
            rs = ps.executeQuery();
            while (rs.next()) {
                ssalt = rs.getString("ssalt");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(rs, ps, conn);
        }
        return ssalt;
    }

    @Override
    public Map<Float, Student> findStudentByClassno(int classno) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        //声明变量
        Student student = null;
        Map<Float, Student> studentFloatMap = new LinkedHashMap<>();
        conn = DBUtil.getConnection();
        String sql = "select distinct student.*, sum(grade) grade from student,sc where sc.sno = student.sno and student.classno = ? group by sc.sno order by sum(grade) desc";
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, classno);
            rs = ps.executeQuery();
            while (rs.next()) {
                int sno = rs.getInt("sno");
                String sname = rs.getString("sname");
                String spassword = rs.getString("spassword");
                String ssalt = rs.getString("ssalt");
                String ssex = rs.getString("ssex");
                int sage = rs.getInt("sage");
                String sphone = rs.getString("sphone");
                float grade = rs.getFloat("grade");
                student = new Student(sno, sname, spassword, ssalt, classno, ssex, sage, sphone);
                studentFloatMap.put(grade, student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(rs, ps, conn);
        }
        return studentFloatMap;
    }
}
